package com.appspheregroup.jj.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedList;


import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class DataBaseHelper extends SQLiteOpenHelper{
	 //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.gokiri.activity/databases/";
 
    private static String DB_NAME = "jjdata4.db" +
    		"";
 
    private SQLiteDatabase myDataBase; 
    
    private final Context myContext;

    //private SQLiteDatabase db;


	public DataBaseHelper(Context context){
		super(context, DB_NAME, null, 1);
        this.myContext = context;

	}
	private boolean checkDataBase(){
		 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 
    	}catch(SQLiteException e){
 
    		//database does't exist yet.
 
    	}
 
    	if(checkDB != null){
 
    		checkDB.close();
 
    	}
 
    	return checkDB != null ? true : false;
    }
	private void copyDataBase() throws IOException{
		 
    	//Open your local db as the input stream
//		myContext.getAssets().list(DB_PATH);
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
 
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
 
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024*8];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
	  public void openDataBase() throws SQLException{
		  
	    	//Open the database
	        String myPath = DB_PATH + DB_NAME;
	    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	    	
	 
	    }
	  @Override
		public synchronized void close() {
	 
	    	    if(myDataBase != null)
	    		    myDataBase.close();
	 
	    	    super.close();
	 
		}
	  public void createDataBase() throws IOException{
		  
	    	boolean dbExist = checkDataBase();
	 
	    	if(dbExist){
	    		//do nothing - database already exist
	    	}else{
	 
	    		//By calling this method and empty database will be created into the default system path
	               //of your application so we are gonna be able to overwrite that database with our database.
	        	this.getReadableDatabase();
	 
	        	try {
	 
	    			copyDataBase();
	 
	    		} catch (IOException e) {
	 
	        		throw new Error("Error copying database");
	 
	        	}
	    	}
	 
	    }


	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	
	public ArrayList<String> getPixel(String secid,String number) {
		
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			if(number.equals("-")){
				return result;
			}
			if(secid==null||number==null){
				return result;
			}
			String queryCmd = "SELECT * FROM map WHERE "+"sec_id = "+secid +" AND "+" num_shop ="+number+";";
			
			
			try {
				c = myDataBase.rawQuery(queryCmd, null);
				if (c.moveToFirst()) {		// Check if our result was valid.
					// Loop through all Results
					do {
						String px = c.getString(c.getColumnIndex("pixel_map_x"));
						String py = c.getString(c.getColumnIndex("pixel_map_y"));
						 ShopFavorite.shop_id= c.getString(c.getColumnIndex("shop_id"));
						 if(Param.is_whereami){
							 Param.WhereamiName = c.getString(c.getColumnIndex("shop_id"));
						 }
						// System.out.println("TITLE : " + pdtitle);					
						result.add(px+"&&"+py);
					} while (c.moveToNext());
				}else {
					return null;
				}
			} catch (Exception e) {
				
				Toast.makeText(myContext, " error: "+e, Toast.LENGTH_SHORT).show();
				
			}
			
			// edit here
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		} catch (CursorIndexOutOfBoundsException ex) {
		
			Toast.makeText(myContext, " error: "+ex, Toast.LENGTH_SHORT).show();
		} catch (Exception ex) {
			Toast.makeText(myContext, " error: "+ex, Toast.LENGTH_SHORT).show();
			
		} 
		

		return result;
	}
	
	public HashSet<String> getListShop_have_promotion(){
		HashSet<String> result = null;
		Cursor c = null;
		result = new HashSet<String>();
		/**select 	s._id shop_id,s.name shopname,
			ps.product_id,
			p.subcat_id,
			p.name product_name,
			sc.name subcat_name
		from 
				shop s ,product_shop_subcat ps, product p, sub_cat sc
		where
				s._id = ps.shop_id
		and		p._id = ps.product_id
		and 	sc._id = p.subcat_id

		and	subcat_name  like '%shirts%'*/
		
		try {
			
			String queryCmd = "SELECT  s._id shop_id, " +
					"		s.name shopname, " +
					"		ps.product_id, " +"ps.promotion_id,"+
					"		p.subcat_id," +
					"		p.name product_name," +
					"		sc.name subcat_name" +
					" FROM	shop s,product_shop_subcat ps,product p,sub_cat sc" +
					" WHERE	s._id = ps.shop_id" +
					" AND	p._id = ps.product_id" +
					" AND	sc._id = p.subcat_id" +
					" AND	ps.promotion_id <> 0;";
				
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String shopname = c.getString(c.getColumnIndex("shopname"));
					if(!result.contains(shopname)){
						result.add(shopname);
					}
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getListShop(){
		ArrayList<String> result = null;
		Cursor c = null;
		result = new ArrayList<String>();
		/**select name from Shop 
			order by name COLLATE NOCASE*/
		
		try {
			
			String queryCmd = "SELECT  name FROM shop ORDER BY name COLLATE NOCASE;";
			
			
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String shopname = c.getString(c.getColumnIndex("name"));
					if(!result.contains(shopname)){
						result.add(shopname);
					}						
				
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getPixel(String nameshop) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "SELECT * FROM map,shop WHERE map.shop_id = shop._id and "+ "name like '%"+nameshop+"%';";
			
			
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String px = c.getString(c.getColumnIndex("pixel_map_x"));
					String py = c.getString(c.getColumnIndex("pixel_map_y"));
					
					result.add(px+"&&"+py);
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getFacilities_pixel(int facilily_id){
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		/**
		 * select 	m.pixel_map_x,
					m.pixel_map_y,
					ft.name
		  from 	facility_type ft,
				facility f,
				map m
		 where 	ft._id = f.facility_type_id
		 and	f.map_id = m._id
		 * */
		try {
			String queryCmd = "select m.pixel_map_x , m.pixel_map_y , ft.name from facility_type ft,facility f, map m " +
					"where ft._id = f.facility_type_id and	f.map_id = m._id " +
					"and ft._id = "+facilily_id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String px = c.getString(c.getColumnIndex("m.pixel_map_x"));
					String py = c.getString(c.getColumnIndex("m.pixel_map_y"));
					
					result.add(px+"&&"+py);
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}

	public ArrayList<String> getcatid_rec() {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "select c._id cat_id,c.name cat_name,s._id shop_id,m._id map_id,m.pixel_map_x,m.pixel_map_y"+ 
			" from category c,shop s,map m ,shop_cat sc"+
			" where sc.cat_id = c._id   and	sc.shop_id = s._id and	m.shop_id = s._id ;";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_id = c.getString(c.getColumnIndex("cat_id"));
					if(!result.contains(cat_id)){
						result.add(cat_id);
					}
					
					
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getcatid_rec_by_sec(String sec) {
		String staticsec ="15";
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "select c._id cat_id,c.name cat_name,s._id shop_id,m._id map_id ,m.sec_id"+ 
			" from category c,shop s,map m ,shop_cat sc"+
			" where sc.cat_id = c._id   and	sc.shop_id = s._id and	m.shop_id = s._id and m.sec_id = "+staticsec+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_id = c.getString(c.getColumnIndex("cat_id"));
					if(!result.contains(cat_id)){
						result.add(cat_id);
					}
					
					
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getPixel_bycatid(int Cat_id) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "select c._id cat_id,c.name cat_name,s._id shop_id,m._id map_id,m.pixel_map_x,m.pixel_map_y"+ 
			" from category c,shop s,map m ,shop_cat sc"+
			" where sc.cat_id = c._id   and	sc.shop_id = s._id and	m.shop_id = s._id and	c._id = "+Cat_id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String px = c.getString(c.getColumnIndex("m.pixel_map_x"));
					String py = c.getString(c.getColumnIndex("m.pixel_map_y"));
					
					result.add(px+"&&"+py);
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getPixel_bycatid_andSEction(int Cat_id) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
			
		}
		/*select * from map,shop_cat ,category
		where map.shop_id = shop_cat .shop_id and shop_cat.cat_id = category._id and map.sec_id =15*/
		try {
			String queryCmd = "select m.pixel_sec_x,m.pixel_sec_y,m.shop_id"+ 
			" from category ,map m ,shop_cat "+
			" where m.shop_id = shop_cat.shop_id and shop_cat.cat_id = category._id and m.sec_id =15 and category._id ="+Cat_id+
			";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String px = c.getString(c.getColumnIndex("m.pixel_sec_x"));
					String py = c.getString(c.getColumnIndex("m.pixel_sec_y"));
					
					result.add(px+"&&"+py);
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} 
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		

		return result;
	}
	public ArrayList<String> getPixel_bycatidOf_Favorite(int Cat_id) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		/*
		 * select
		f.map_id,
		f.cat_id,
		m.pixel_map_x,
		m.pixel_map_y
		from
		favorite f,
		map m
		where
		f.map_id = m._id*/
		try {
			String queryCmd = "select f.map_id,f.cat_id,m.pixel_map_x,m.pixel_map_y" +
					" FROM favorite f,map m" +
					" WHERE f.map_id = m._id" +
					" AND f.cat_id = "+Cat_id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String px = c.getString(c.getColumnIndex("m.pixel_map_x"));
					String py = c.getString(c.getColumnIndex("m.pixel_map_y"));
					
					result.add(px+"&&"+py);
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getPixel_bycatidOf_Favorite_andSEction(int Cat_id) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		/*
		 * select
		f.map_id,
		f.cat_id,
		m.pixel_map_x,
		m.pixel_map_y
		from
		favorite f,
		map m
		where
		f.map_id = m._id*/
		try {
			String queryCmd = "select f.map_id,f.cat_id,m.pixel_sec_x,m.pixel_sec_y, m.sec_id" +
					" FROM favorite f,map m" +
					" WHERE f.map_id = m._id" +
					" AND m.sec_id = 15 AND f.cat_id = "+Cat_id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String px = c.getString(c.getColumnIndex("m.pixel_sec_x"));
					String py = c.getString(c.getColumnIndex("m.pixel_sec_y"));
					
					result.add(px+"&&"+py);
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} 
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		

		return result;
	}
	public ArrayList<String> getSection() {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "SELECT * FROM section;";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_name = c.getString(c.getColumnIndex("name"));					
					if(!result.contains(cat_name)){
						result.add(cat_name);
					}	
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			return result;
			
		} catch (Exception ex) {
			return result;
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getCat() {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "SELECT * FROM category;";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_name = c.getString(c.getColumnIndex("name"));					
					if(!result.contains(cat_name)){
						result.add(cat_name);
					}	
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			return result;
			
		} catch (Exception ex) {
			return result;
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}

	public String getCatname(int cat_id) {
		String result = null;
		Cursor c = null;
		
		
		try {
			String queryCmd = "SELECT * FROM category where _id = "+ cat_id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					result = c.getString(c.getColumnIndex("name"));					
					
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			return result;
			
		} catch (Exception ex) {
			return result;
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public String getFacilitiesname(int _id) {
		String result = null;
		Cursor c = null;
		
		
		try {
			String queryCmd = "SELECT * FROM facility_type where _id = "+ _id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					result = c.getString(c.getColumnIndex("name"));					
					
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			return result;
			
		} catch (Exception ex) {
			return result;
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	
	public ArrayList<String> getSub_Cat(int cat_id) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		try {
			String queryCmd = "SELECT * FROM sub_cat " +
					"WHERE cat_id = "
					+cat_id+";";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_name = c.getString(c.getColumnIndex("name"));					
					if(!result.contains(cat_name)){
						result.add(cat_name);
					}	
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public HashSet<String> getShopFromCat_id(int Cat_id) {
		HashSet<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new HashSet<String>();
		}
		/** 
		 * 
		 * select 	s._id shop_id,s.name shopname,
			ps.product_id,
			p.subcat_id,
			p.name product_name,
			sc.name subcat_name,
			sc.cat_id
			from 
			shop s ,product_shop_subcat ps, product p, sub_cat sc, category c
			where
			s._id = ps.shop_id
			and	p._id = ps.product_id
			and 	sc._id = p.subcat_id
			and	c._id = sc.cat_id

			and	cat_id  =  ...*/
		try {
//			String queryCmd = " select 	s._id shop_id,s.name shopname,"+
//			"ps.product_id,"+
//			"p.subcat_id,"+
//			"p.name product_name,"+
//			"sc.name subcat_name,"+
//			"sc.cat_id"+
//			" from "+
//			"shop s ,product_shop_subcat ps, product p, sub_cat sc, category c"+
//			" where"+
//			" s._id = ps.shop_id"+
//			" and	p._id = ps.product_id"+
//			" and 	sc._id = p.subcat_id"+
//			" and	c._id = sc.cat_id"+
//
//			" and	cat_id  =" +
//			Cat_id+";";
			//--------
			/**select 	c._id cat_id,
			c.name cat_name,
			s._id shop_id,
			s.name shop_name,
			m._id map_id,
			m.pixel_map_x,
			m.pixel_map_y
		from 
			category c,
			shop s,
			map m
		where
			s.cat_id = c._id
		and	m.shop_id = s._id
		and cat_name = ''
*/
			//--------
			String queryCmd = "select c._id cat_id,c.name cat_name,s._id shop_id,m._id map_id,m.pixel_map_x,m.pixel_map_y " +
					"from category c,shop s,map m " +
					"where s.cat_id = c._id and	m.shop_id = s._id and "+
					"cat_id = "+Cat_id+";";
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_name = c.getString(c.getColumnIndex("shopname"));	
					if(!result.contains(cat_name)){
						result.add(cat_name);
					}	
				
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public HashSet<String> getShopFromFavorite() {
		HashSet<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new HashSet<String>();
		}
		/** 
		 * 
		 select distinct shop_name,shop._id from favorite,shop ,promotion
		where shop.name = favorite.shop_name
		and promotion.shop_id = shop._id
		 */
		try {
//			String queryCmd = "select distinct shop_name,shop._id from favorite,shop ,promotion "+
//			"where shop.name = favorite.shop_name and promotion.shop_id = shop._id ;"	;
			//--------
			String queryCmd = "select distinct shop_name,shop._id from favorite,shop "+
			"where shop.name = favorite.shop_name ;"	;
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_name = c.getString(c.getColumnIndex("shop_name"));	
					if(!result.contains(cat_name)){
						result.add(cat_name);
					}	
				
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getSearchbyShopName(String shopname) {
		ArrayList<String> result = null;
		Cursor c = null;
		if (result == null) {
			result = new ArrayList<String>();
		}
		/** 
		 * 
		 * select name from shop order by name asc*/
		try {
			String queryCmd = "SELECT name FROM Shop " +
					"WHERE name like '%"
					+shopname+"%';";
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String cat_name = c.getString(c.getColumnIndex("name"));
					if(!result.contains(cat_name)){
						result.add(cat_name);
					}
					
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	
	public ArrayList<String> getListShop(String subcat){
		ArrayList<String> result = null;
		Cursor c = null;
		result = new ArrayList<String>();
		/**select 	s._id shop_id,s.name shopname,
			ps.product_id,
			p.subcat_id,
			p.name product_name,
			sc.name subcat_name
		from 
				shop s ,product_shop_subcat ps, product p, sub_cat sc
		where
				s._id = ps.shop_id
		and		p._id = ps.product_id
		and 	sc._id = p.subcat_id

		and	subcat_name  like '%shirts%'*/
		
		try {
			
			String queryCmd = "SELECT  s._id shop_id, " +
					"		s.name shopname, " +
					"		ps.product_id, " +
					"		p.subcat_id," +
					"		p.name product_name," +
					"		sc.name subcat_name" +
					" FROM	shop s,product_shop_subcat ps,product p,sub_cat sc" +
					" WHERE	s._id = ps.shop_id" +
					" AND	p._id = ps.product_id" +
					" AND	sc._id = p.subcat_id" +
					" AND	subcat_name like '%"
					 +subcat+"%';";
			
			
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String shopname = c.getString(c.getColumnIndex("shopname"));
					if(!result.contains(shopname)){
						result.add(shopname);
					}					
				
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ArrayList<String> getListNum_bysection(int section){
		ArrayList<String> result = null;
		Cursor c = null;
		result = new ArrayList<String>();
		/*select num_shop from map where sec_id =1*/
		
		try {
			
			String queryCmd ="select num_shop from map where sec_id = "+section+";";
			
			
			c = myDataBase.rawQuery(queryCmd, null);

			// Check if our result was valid.
		
			
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String num_shop = c.getString(c.getColumnIndex("num_shop"));
					if(!result.contains(num_shop)&&num_shop!=null){
						result.add(num_shop);
					}					
				
				} while (c.moveToNext());
			}
		} catch (CursorIndexOutOfBoundsException ex) {
			Toast.makeText(myContext, "CursorIndexError", Toast.LENGTH_SHORT).show();
			
		} catch (Exception ex) {
			Toast.makeText(myContext, "Exception ex", Toast.LENGTH_SHORT).show();
		} finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return result;
	}
	public ShopData getShopname_bypress_pin(float pixelX,float pixelY){
		/**
		 * select shop.name from map,shop
		where  
			map.shop_id = shop._id 
		and
			pixel_map_x = 1215.67 and pixel_map_y = 253.67*/
		Cursor c = null;
		String queryCmd = "SELECT shop.name,map.sec_id,map.num_shop FROM map , shop " +
				"WHERE map.shop_id = shop._id " +
				"and pixel_map_x = "+pixelX+
				" AND pixel_map_y = "+pixelY+" ;";
		c = myDataBase.rawQuery(queryCmd, null);
		ShopData shopdata = new ShopData();
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String name = c.getString(c.getColumnIndex("name"));
					shopdata.shopname = name;	
					Param.Nameshop=name;
					String section = c.getString(c.getColumnIndex("sec_id"));
					shopdata.shop_section=section;					
					Param.Section = section;
					String num = c.getString(c.getColumnIndex("num_shop"));
					shopdata.shop_num=num;
					Param.Number = num;
					String desc = c.getString(c.getColumnIndex("description"));
					shopdata.shopdesc=desc;
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			
		}
			catch (Exception e) {
			// TODO: handle exception
		}
			finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return shopdata;
	} 
	public ShopData getShopname_bypress_pin_andSEction(float pixelX,float pixelY){
		/**
		 * select shop.name from map,shop
		where  
			map.shop_id = shop._id 
		and
			pixel_map_x = 1215.67 and pixel_map_y = 253.67*/
		Cursor c = null;
		String queryCmd = "SELECT shop.name,map.sec_id,map.num_shop FROM map , shop " +
				"WHERE map.shop_id = shop._id " +
				"and map.sec_id = 15 and pixel_sec_x = "+pixelX+
				" AND pixel_sec_y = "+pixelY+" ;";
		c = myDataBase.rawQuery(queryCmd, null);
		ShopData shopdata = new ShopData();
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String name = c.getString(c.getColumnIndex("name"));
					shopdata.shopname = name;	
					Param.Nameshop=name;
					String section = c.getString(c.getColumnIndex("sec_id"));
					shopdata.shop_section=section;					
					Param.Section = section;
					String num = c.getString(c.getColumnIndex("num_shop"));
					shopdata.shop_num=num;
					Param.Number = num;
					String desc = c.getString(c.getColumnIndex("description"));
					shopdata.shopdesc=desc;
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			
		}
			catch (Exception e) {
			// TODO: handle exception
		}
			finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return shopdata;
	} 
	public String getShopname(String sec_id,String num_shop){
		/**
		 * select shop.name from map,shop
		where  
			map.shop_id = shop._id 
		and
			pixel_map_x = 1215.67 and pixel_map_y = 253.67*/
		Cursor c = null;
		String queryCmd = "SELECT shop.name,map.sec_id,map.num_shop FROM map , shop " +
				"WHERE map.shop_id = shop._id " +
				"and sec_id = "+sec_id+
				" AND num_shop = "+num_shop+" ;";
		c = myDataBase.rawQuery(queryCmd, null);
		ShopData shopdata = new ShopData();
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String name = c.getString(c.getColumnIndex("name"));
					shopdata.shopname = name;	
					Param.Nameshop=name;
					String section = c.getString(c.getColumnIndex("sec_id"));
					shopdata.shop_section=section;					
					Param.Section = section;
					String num = c.getString(c.getColumnIndex("num_shop"));
					shopdata.shop_num=num;
					Param.Number = num;
					String desc = c.getString(c.getColumnIndex("description"));
					shopdata.shopdesc=desc;
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			
		}
			catch (Exception e) {
			// TODO: handle exception
		}
			finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return Param.Nameshop;
	} 
	public String getpromotionbyshopname(String shopname){
		/**
		 * select promotion.description from shop ,promotion 
		where shop._id = promotion.shop_id*/
		Cursor c = null;
		String queryCmd = "select promotion.description from shop ,promotion  " +
				"where shop._id = promotion.shop_id" +
				" AND shop.name = '"+shopname+"' ;";
		c = myDataBase.rawQuery(queryCmd, null);
		
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String promotion = c.getString(c.getColumnIndex("promotion.description"));
					Param.PROMOTION = promotion;	
					
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			
		}
			catch (Exception e) {
			// TODO: handle exception
		}
			finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

		return Param.PROMOTION;
	} 
	public void setShop_num_sec(String shopname){
		/**
		 * select shop.name from map , shop , promotion 
		where map.shop_id = shop._id and shop._id = promotion.shop_id
		and  shop.name = '2Pm'*/
		Cursor c = null;
		Cursor cc=null;
		String queryCmd = "select shop.name ,shop.phone, map.sec_id , map.num_shop from map , shop  " +
				"where map.shop_id = shop._id "+
				" and shop.name = '"+shopname+"';";
		c = myDataBase.rawQuery(queryCmd, null);
		
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String name = c.getString(c.getColumnIndex("shop.name"));
					Param.Shopname = name;
					Param.Nameshop = name;
					String sec_id = c.getString(c.getColumnIndex("map.sec_id"));
					Param.Section =sec_id;
					String num = c.getString(c.getColumnIndex("map.num_shop"));
						
					Param.Number =num;
					String phone = c.getString(c.getColumnIndex("shop.phone"));
					Param.PHONE =phone;
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			Toast.makeText(myContext, "error :"+ex, Toast.LENGTH_SHORT).show();
		}
			catch (Exception e) {
				Toast.makeText(myContext, "error :"+e, Toast.LENGTH_SHORT).show();
		}
			finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

	} 
	public void setShop_favorite(float pixelX,float pixelY){
		/**
		 * select shop.name,map._id,shop.cat_id from map,shop
		where  
			map.shop_id = shop._id 
		and
			pixel_map_x = 1215.67 and pixel_map_y = 253.67*/
		Cursor c = null;
		Cursor cc=null;
		String queryCmd = "select c._id cat_id,c.name cat_name,s._id shop_id,s.name,m._id map_id,m.pixel_map_x,m.pixel_map_y " +
				"from category c,shop s,map m,shop_cat sc where sc.cat_id = c._id and sc.shop_id = s._id and	" +
				"m.shop_id = s._id "+
				"and pixel_map_x = "+pixelX+ " AND pixel_map_y ="  +pixelY+";";
		c = myDataBase.rawQuery(queryCmd, null);
		
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String name = c.getString(c.getColumnIndex("s.name"));
					ShopFavorite.shop_name = name;	
					String cat_id = c.getString(c.getColumnIndex("cat_id"));
					ShopFavorite.cat_id=cat_id;
					String map_id = c.getString(c.getColumnIndex("map_id"));
					ShopFavorite.map_id=map_id;
					LinkedList<String > List_cat_id = new LinkedList<String>();
					List_cat_id.addAll(get_cat_id_favorite());
					int max = 0;
					if(List_cat_id.size()==0){
						max=0;
					}else{
						
						for(int i=0;i<List_cat_id.size();i++){
							int id = Integer.parseInt(List_cat_id.get(i)) ;
							if(id > max){
								max = id;
							}
						}
					}
					
					
						updatefavorite_row((max+1), Integer.parseInt(ShopFavorite.map_id), ShopFavorite.shop_name,Integer.parseInt(ShopFavorite.cat_id) );
					
				} while (c.moveToNext());
			}else {
				Toast.makeText(myContext, "cant add shop to favorite : shop is not in shop_cat TABLE", Toast.LENGTH_SHORT).show();
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			Toast.makeText(myContext, "error :"+ex, Toast.LENGTH_SHORT).show();
		}
			catch (Exception e) {
				Toast.makeText(myContext, "error :"+e, Toast.LENGTH_SHORT).show();
		}
			
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			
		}

	} 
	public void setShop_favorite_bysearchaddr(float pixelX,float pixelY){
		/**
		 * select name,map._id from map,shop 
			where shop._id = map.shop_id
			and pixel_map_x =1215.67 and pixel_map_y = 253.67*/
		Cursor c = null;
		Cursor cc=null;
		String queryCmd = "select name,map._id from map,shop where shop._id = map.shop_id"+
				" and pixel_map_x = "+pixelX+ " AND pixel_map_y ="  +pixelY+";";
		c = myDataBase.rawQuery(queryCmd, null);
		
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					String name = c.getString(c.getColumnIndex("name"));
					ShopFavorite.shop_name = name;	
					String map_id = c.getString(c.getColumnIndex("map._id"));
					ShopFavorite.map_id=map_id;
				
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			Toast.makeText(myContext, "error :"+ex, Toast.LENGTH_SHORT).show();
		}
			catch (Exception e) {
				Toast.makeText(myContext, "error :"+e, Toast.LENGTH_SHORT).show();
		}
			finally {
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		}

	} 
	public HashSet<String> get_cat_id_favorite(){
		HashSet<String> result = null;
		Cursor c = null;
		result = new HashSet<String>();
		/**
		 * select distinct cat_id from favorite*/
		
		String queryCmd = "SELECT distinct cat_id from favorite;";
		c = myDataBase.rawQuery(queryCmd, null);
		
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					
					String cat_id = c.getString(c.getColumnIndex("cat_id"));
					if(cat_id!=null){
						result.add(cat_id);
						
					}
					
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			
		}
			catch (Exception e) {
		
		}
			
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		
			return result;
	} 
	public HashSet<String> get_cat_id_favorite_bysec(){
		HashSet<String> result = null;
		Cursor c = null;
		result = new HashSet<String>();
		/**
		 * SELECT favorite.cat_id from favorite,map where map._id = favorite.map_id and map.sec_id = 15*/
		
		String queryCmd = "SELECT favorite.cat_id from favorite,map where map._id = favorite.map_id and map.sec_id = 15;";
		c = myDataBase.rawQuery(queryCmd, null);
		
	
		try {
			if (c.moveToFirst()) {
				// Loop through all Results
				do {
					
					String cat_id = c.getString(c.getColumnIndex("favorite.cat_id"));
					if(cat_id!=null){
						result.add(cat_id);
						
					}
					
				} while (c.moveToNext());
			}
		}  catch(CursorIndexOutOfBoundsException ex){
			
		}
			catch (Exception e) {
		
		}
			
			if (c.moveToFirst()) {
				c.deactivate();
				c.close();
				c = null;
			}
		
			return result;
	} 
	
	public String get_cat_name(String _id){
		String result = null;
		Cursor c = null;
	
		/**
		 * select distinct cat_id from favorite*/
		if(_id!=null){
			String queryCmd = "SELECT name from category where _id ="+_id+";";
			

			try {
				c = myDataBase.rawQuery(queryCmd, null);
				if (c.moveToFirst()) {
					// Loop through all Results
					do {
						
						String name = c.getString(c.getColumnIndex("name"));
						result = name;
						
					} while (c.moveToNext());
				}
			}  catch(CursorIndexOutOfBoundsException ex){
				
			}
				catch (Exception e) {
					int a;
					a= 5;
			}
				finally {
				if (c.moveToFirst()) {
					c.deactivate();
					c.close();
					c = null;
				}
			}
		}
		
	
			return result;
	} 
	
	public void updatefavorite_row(int _id,int map_id,String shop_name,int cat_id){
		
		ContentValues initialValues = new ContentValues();
		//initialValues.put("_id", _id);
		initialValues.put("map_id", map_id);
		initialValues.put("shop_name", shop_name);
		initialValues.put("cat_id", cat_id);
		long a = myDataBase.insert("favorite", null, initialValues);
		
	}
}
